package DAO.MySQL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DAO.DAOException;
import DAO.EmployeeDAO;
import Model.Employee;
import Util.JDBCUtil;


public class EmployeeDAOImpl implements EmployeeDAO{
	private Connection con;
	public Employee findEmployee(String name) throws DAOException {
		Employee employee=new Employee();
		con=JDBCUtil.GetConnection();
		String selectsql="Select * from Employee where loginname= '" + name.trim() +"';" ;
		try{
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(selectsql);
			while(rs.next()){
				employee.setEmplyeename(rs.getString("name"));
				employee.setLoginname(rs.getString("loginname"));
				employee.setPassword(rs.getString("passwd"));
				employee.setRoleid(rs.getInt("roleid"));
			}
			st.close();		
		}catch(Exception e){
			String error="error selecting employees.Nested Exception is"+ e;
			throw new DAOException(error);
		}finally{
			try{
				con.rollback();
				con.close();
			}catch(Exception e){}
		}
		System.out.println(employee.toString());
		return employee;
	}
	
	public ArrayList<Employee> findAllEmployee() throws DAOException {
		// TODO Auto-generated method stub
		ArrayList<Employee> employeelist=new ArrayList<Employee>();
		String selectsql="Select * from Employee;";
		con=JDBCUtil.GetConnection();
		try{
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(selectsql);
			while(rs.next()){
				Employee employee=new Employee();
				employee.setEmplyeename(rs.getString("name"));
				employee.setLoginname(rs.getString("loginname"));
				employee.setPassword(rs.getString("passwd"));
				employee.setRoleid(rs.getInt("roleid"));
				employeelist.add(employee);
			}
			st.close();		
		}catch(Exception e){
			String error="error selecting employees.Nested Exception is"+ e;
			throw new DAOException(error);
		}finally{
			try{
				con.rollback();
				con.close();
			}catch(Exception e){}
		}
		return employeelist;
	}
	
	public void insertEmployee(Employee emp) throws DAOException {
		// TODO Auto-generated method stub
		String insertsql="Insert into Employee values('" +  emp.getLoginname() + "','" + emp.getPassword() + "','"
		+ emp.getEmplyeename() + "','" + emp.getRoleid() + "');" ;
		con=JDBCUtil.GetConnection();
		try{
			Statement st=con.createStatement();
			st.executeUpdate(insertsql);
			st.close();
		}catch(Exception e){
			String error="Error inserting new employee.Error is" + e;
			throw new DAOException(error);
		}finally{
			try{
				con.rollback();
				con.close();
			}catch(Exception e){}
		}		
	}
	
	public void deleteEmployee(String loginname) throws DAOException {
		// TODO Auto-generated method stub
		String login=loginname;
		String deletesql="Delete from Employee where loginname='" + login + "';" ;
		con=JDBCUtil.GetConnection();
		try{
			Statement st=con.createStatement();
			st.executeUpdate(deletesql);
			st.close();
		}catch(Exception e){
			String error="Error deleting employee.Error is" + e;
			throw new DAOException(error);
		}finally{
			try{
				con.rollback();
				con.close();
			}catch(Exception e){}
		}		
	}
}
